package it.rockliveitalia.database;

import it.rockliveitalia.database.EventContract.EventTable;
import it.rockliveitalia.entity.Event;

import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class EventDbHelper extends SQLiteOpenHelper {

	public static final int DATABASE_VERSION = 1;
	public static final String DATABASE_NAME = "rockLiveIT.db";

	public EventDbHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String createSql = "CREATE TABLE {0} ({1} TEXT PRIMARY KEY, {2} TEXT, {3} TEXT, {4} REAL,"
				+ "{5} TEXT, {6} TEXT, {7} TEXT, {8} TEXT, {9} TEXT, {10} TEXT, {11} TEXT, {12} TEXT, "
				+ "{13} TEXT, {14} TEXT);";

		db.execSQL(MessageFormat.format(createSql, EventTable.TABLE_NAME,
				EventTable.ID, EventTable.VENUE_NAME, EventTable.VENUE_SITE,
				EventTable.DATE, EventTable.STREET, EventTable.CITY,
				EventTable.PROVINCIA, EventTable.BAND, EventTable.BAND_SITE,
				EventTable.DESCRIPTION, EventTable.IMAGE, EventTable.TAG,
				EventTable.LAT, EventTable.LNG));
	}

	@SuppressLint("NewApi")
	public void insertEvent(Event e) {
		SQLiteDatabase db = getWritableDatabase();
		Calendar cal = Calendar.getInstance();
		cal.set(Calendar.DAY_OF_YEAR, cal.get(Calendar.DAY_OF_YEAR) - 1);
		if (!e.getBand().equals("") || !e.getVenueName().equals("")
				|| !e.getCity().equals("") || !e.getStreet().equals("")
				|| !e.getProvincia().equals("")) {
			if (e.getDate().getTime() > cal.getTimeInMillis()) {
				ContentValues v = new ContentValues();
				v.put(EventTable.ID, e.getId());
				v.put(EventTable.VENUE_NAME, e.getVenueName());
				v.put(EventTable.VENUE_SITE, e.getVenueSite());
				v.put(EventTable.DATE, e.getDate().getTime());
				v.put(EventTable.STREET, e.getStreet());
				v.put(EventTable.CITY, e.getCity());
				v.put(EventTable.PROVINCIA, e.getProvincia());
				v.put(EventTable.BAND, e.getBand());
				v.put(EventTable.BAND_SITE, e.getBandSite());
				v.put(EventTable.DESCRIPTION, e.getDescription());
				v.put(EventTable.IMAGE, e.getImage());
				v.put(EventTable.TAG, e.getTag());
				v.put(EventTable.LAT, e.getLat());
				v.put(EventTable.LNG, e.getLng());
				if (android.os.Build.VERSION.SDK_INT >= 8) {
					db.insertWithOnConflict(EventTable.TABLE_NAME, null, v,
							SQLiteDatabase.CONFLICT_REPLACE);

				} else {
					Cursor c = getEventById(e.getId());
					try {
						if (c != null) {
							db.insert(EventTable.TABLE_NAME, null, v);
						} else {
							String where = EventTable.ID + " LIKE ?";
							String[] arg = { e.getId() };
							db.update(EventTable.TABLE_NAME, v, where, arg);
						}
					} catch (Exception exception) {
						exception.printStackTrace();
						// empty result
					}
				}

			}
		}

	}

	public Cursor getEvents() {
		return getReadableDatabase().query(EventTable.TABLE_NAME,
				EventTable.COLUMNS, null, null, null, null, EventTable.DATE);
	}

	public Cursor getEventById(String id) {
		String[] arg = { id };
		return getReadableDatabase().query(EventTable.TABLE_NAME,
				EventTable.COLUMNS, EventTable.ID + " LIKE ?", arg, null, null,
				EventTable.DATE, "1");
	}

	@SuppressLint("NewApi")
	public Cursor getBands() {
		String[] projection = { EventTable.BAND };
		if (android.os.Build.VERSION.SDK_INT >= 16) {
			return getReadableDatabase().query(true, EventTable.TABLE_NAME,
					projection, null, null, null, null, EventTable.BAND, null,
					null);
		} else {
			return getReadableDatabase().query(EventTable.TABLE_NAME,
					projection, null, null, null, null, EventTable.DATE);
		}

	}

	@SuppressLint("NewApi")
	public Cursor getVenues() {
		String[] projection = { EventTable.VENUE_NAME };
		if (android.os.Build.VERSION.SDK_INT >= 16) {

			return getReadableDatabase().query(true, EventTable.TABLE_NAME,
					projection, null, null, null, null, EventTable.VENUE_NAME,
					null, null);
		} else {
			return getReadableDatabase().query(EventTable.TABLE_NAME,
					projection, null, null, null, null, EventTable.DATE);
		}
	}

	public Cursor getIDsFromBand(String band) {
		String[] prospection = { EventTable.ID };
		String[] arg = { "%" + band + "%" };
		return getReadableDatabase().query(EventTable.TABLE_NAME, prospection,
				EventTable.BAND + " LIKE ?", arg, null, null, EventTable.DATE,
				null);
	}

	public Cursor getIDsFromBandName(String s) {
		String[] prospection = { EventTable.ID };
		String[] arg = { s };
		return getReadableDatabase().query(EventTable.TABLE_NAME, prospection,
				EventTable.BAND + " LIKE ?", arg, null, null, EventTable.DATE,
				null);
	}

	public Cursor getIDsFromVenue(String venue) {
		String[] prospection = { EventTable.ID };
		String[] arg = { "%" + venue + "%" };
		return getReadableDatabase().query(EventTable.TABLE_NAME, prospection,
				EventTable.VENUE_NAME + " LIKE ?", arg, null, null,
				EventTable.DATE, null);
	}

	public Cursor getIDsFromVenueName(String s) {
		String[] prospection = { EventTable.ID };
		String[] arg = { s };
		return getReadableDatabase().query(EventTable.TABLE_NAME, prospection,
				EventTable.VENUE_NAME + " LIKE ?", arg, null, null,
				EventTable.DATE, null);
	}

	public List<String> cursorToStrings(Cursor c) {
		List<String> list = new ArrayList<String>();
		try {
			c.moveToFirst();
			do {
				String result = c.getString(0);
				if (!list.contains(result))
					list.add(result);
			} while (c.moveToNext());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;

	}

	public List<Event> cursorToEvents(Cursor c) {
		List<Event> list = new ArrayList<Event>();
		try {
			c.moveToFirst();
			do {
				Event e = new Event();
				e.setId(c.getString(0));
				e.setVenueName(c.getString(1));
				e.setVenueSite(c.getString(2));
				Date d = new Date(c.getLong(3));
				e.setDate(d);
				e.setStreet(c.getString(4));
				e.setCity(c.getString(5));
				e.setProvincia(c.getString(6));
				e.setBand(c.getString(7));
				e.setBandSite(c.getString(8));
				e.setDescription(c.getString(9));
				e.setImage(c.getString(10));
				e.setTag(c.getString(11));
				e.setLat(c.getString(12));
				e.setLng(c.getString(13));
				list.add(e);
			} while (c.moveToNext());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}

	public void deleteOldEvents(long date) {
		String[] arg = { String.valueOf(date) };
		getWritableDatabase().delete(EventTable.TABLE_NAME,
				EventTable.DATE + " < ?", arg);
	}

	public Cursor getEventsByInterval(long date1, long date2) {
		String[] projection = { EventTable.ID };
		String[] args = { String.valueOf(date1), String.valueOf(date2) };
		return getWritableDatabase().query(EventTable.TABLE_NAME, projection,
				EventTable.DATE + " > ? AND " + EventTable.DATE + " < ?", args,
				null, null, EventTable.DATE, null);

	}

}
